# coding=utf-8
import json
import getopt
import os
import sys
import MySQLdb

# MySQL相关配置，需根据实际情况作出修改
mysql_host = "hadoop102"
mysql_port = "3306"
mysql_user = "root"
mysql_passwd = "123456"

# HDFS NameNode相关配置，需根据实际情况作出修改
hdfs_nn_host = "hadoop102"
hdfs_nn_port = "8020"

# 生成配置文件的目标路径，可根据实际情况作出修改
output_path = "/opt/module/datax/job/import"

# 获取mysql的连接
def get_connection():
    return MySQLdb.connect(host=mysql_host, port=int(mysql_port), user=mysql_user, passwd=mysql_passwd)

# 根据数据库名称和表名称获取该表的元数据
# 主要是该表的字段名称和字段类型
def get_mysql_meta(database, table):
    connection = get_connection()
    cursor = connection.cursor()
    sql = """
    SELECT COLUMN_NAME,DATA_TYPE
    from information_schema.COLUMNS
    WHERE TABLE_SCHEMA=%s
        AND TABLE_NAME=%s
    ORDER BY ORDINAL_POSITION
    """
    cursor.execute(sql, [database, table])
    fetchall = cursor.fetchall()
    cursor.close()
    connection.close()
    return fetchall

# 根据数据库名称和表名称获取该表的列名称
def get_mysql_columns(database, table):
    # map方法的第二个参数是一个迭代器，第一个参数是如果操作第二个参数的函数
    # 这里就是取到get_mysql_meta函数返回结果中的第一列的值，即该表的列名称
    return map(lambda x: x[0], get_mysql_meta(database, table))

# 根据数据库名称和表名称获取该表对应hive的列类型
def get_hive_columns(database, table):
    # 根据mysql中的字段类型获取对应在hive中的字段类型
    def type_mapping(mysql_type):
        mappings = {
            "bigint": "bigint",
            "int": "bigint",
            "smallint": "bigint",
            "tinyint": "bigint",
            "decimal": "string",
            "double": "double",
            "float": "float",
            "binary": "string",
            "char": "string",
            "varchar": "string",
            "datetime": "string",
            "time": "string",
            "timestamp": "string",
            "date": "string",
            "text": "string"
        }
        return mappings[mysql_type]
    # 获取元数据
    meta = get_mysql_meta(database, table)
    # 第二个参数是元数据，有两个字段，分别是mysql的字段名和mysql的类型
    # 第一个参数是如何处理第二个参数，在这里返回了一个json
    # {"name": mysql字段名, "type": hive字段类型}
    return map(lambda x: {"name": x[0], "type": type_mapping(x[1].lower())}, meta)

# 生成json文件，即datax的配置文件
def generate_json(source_database, source_table):
    job = {
        "job": {
            "setting": {
                "speed": {
                    "channel": 3
                },
                "errorLimit": {
                    "record": 0,
                    "percentage": 0.02
                }
            },
            "content": [{
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": mysql_user,
                        "password": mysql_passwd,
                        "column": get_mysql_columns(source_database, source_table),
                        "splitPk": "",
                        "connection": [{
                            "table": [source_table],
                            "jdbcUrl": ["jdbc:mysql://" + mysql_host + ":" + mysql_port + "/" + source_database]
                        }]
                    }
                },
                "writer": {
                    "name": "hdfswriter",
                    "parameter": {
                        "defaultFS": "hdfs://" + hdfs_nn_host + ":" + hdfs_nn_port,
                        "fileType": "text",
                        "path": "${targetdir}",
                        "fileName": source_table,
                        "column": get_hive_columns(source_database, source_table),
                        "writeMode": "append",
                        "fieldDelimiter": "\t",
                        "compress": "gzip"
                    }
                }
            }]
        }
    }
    # 如果目标路径不存在，那么就创建
    if not os.path.exists(output_path):
        os.makedirs(output_path)
    # 拼接数据库名和表名成为配置文件的最终名称
    with open(os.path.join(output_path, ".".join([source_database, source_table, "json"])), "w") as f:
        # 生成json文件
        json.dump(job, f)

def main(args):
    source_database = ""
    source_table = ""
    # 获取运行命令中的-d参数和-t参数，分别作为数据库名称和表名称
    options, arguments = getopt.getopt(args, '-d:-t:', ['sourcedb=', 'sourcetbl='])
    for opt_name, opt_value in options:
        if opt_name in ('-d', '--sourcedb'):
            source_database = opt_value
        if opt_name in ('-t', '--sourcetbl'):
            source_table = opt_value
    # 调用方法生成json文件
    generate_json(source_database, source_table)

if __name__ == '__main__':
    main(sys.argv[1:])